﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class Profile, define el perfil de roles para un usuario
/// </summary>

#region Class

public class clsProfile : DAC
{
    #region Variables

    private int idProfile, idRole, idProgram;
    private const String tabla = "dbo.tblProfile";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsProfile() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords()
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idProfile) As NUMBER, ";
        query += tabla + ".idProfile As ID, " + tabla + ".idRole As ROLE, " + tabla + ".idProgram As PROGRAM ";
        query += "FROM " + tabla;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultID(String id)
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idProfile) As NUMBER, ";
        query += tabla + ".idProfile As ID, " + tabla + ".idRole As ROLE, " + tabla + ".idProgram As PROGRAM ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idProfile =" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultProgramID(String id)
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idProfile) As NUMBER, ";
        query += tabla + ".idProfile As ID, " + tabla + ".idRole As ROLE, " + tabla + ".idProgram As PROGRAM ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idProgram =" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultProgramIDROLE(String idRole)
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idProfile) As NUMBER, ";
        query += tabla + ".idProfile As ID, " + tabla + ".idRole As ROL, " + tabla + ".idProgram As PROGRAM, dbo.tblProgram.sName As NAMEPROGRAM ";
        query += "FROM " + tabla + ", dbo.tblProgram ";
        query += "WHERE " + tabla + ".idRole =" + idRole;
        query += " AND " + tabla + ".idProgram = dbo.tblProgram.idProgram";
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultRoleID(String idRole)
    {
        String query = "SELECT ";
        query += tabla + ".idProfile As ID, " + tabla + ".idRole As ROLE, " + tabla + ".idProgram As PROGRAM ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idRole =" + idRole;
        return this.ExecuteQuery(query);
    }

    #endregion

    public int Insert()
    {
        String query = "INSERT INTO " + tabla;
        query += "(idRole, idProgram) ";
        query += "VALUES ('{0}','{1}')";

        String sql = String.Format(query, this.IdRole, this.IdProgram);
        return this.ExecuteDML(sql, "no");
    }

    public int Update()
    {
        String query = "UPDATE " + tabla + " SET ";
        query += tabla + ".idRole=" + this.IdRole + ", " + tabla + ".idProgram=" + this.IdProgram;
        query += " WHERE ";
        query += tabla + ".idProfile=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idProfile={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql,"no");
    }
    
    public int DeleteIDrole_IDprogram()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idRole={0} ";
        query += "AND " + tabla + ".idProgram={1}";
        String sql = String.Format(query, this.IdRole, this.IdProgram);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters

    public int IdProgram
    {
        get { return idProgram; }
        set { idProgram = value; }
    }

    public int IdRole
    {
        get { return idRole; }
        set { idRole = value; }
    }

    public int ID
    {
        get { return idProfile; }
        set { idProfile = value; }
    }

    #endregion

}

#endregion